<?php

require("./mpcamera/setup.php");

$smarty = new Smarty_MPCamera();
set_time_limit(120);

$action = strtolower(filter_input(INPUT_GET, "action"));
$id = (int) $smarty->clean(strtolower(filter_input(INPUT_GET, "id")), true);
$content = strtolower(filter_input(INPUT_GET, "content"));

$smarty->assign("action", $action);

if ($action == "report") {
    // Not submit
    $submit = strtolower(filter_input(INPUT_POST, "submit"));

    if (empty($submit)) {
        $smarty->display("export.tpl");
        die();
    }
    // Query
    $date = strtolower(filter_input(INPUT_POST, "dateExport"));
    
    if (1) {
        $query = "
        SELECT  `diadiem`.`MaDD`, `diadiem`.`TenDD`, CONCAT(`DiaChi`, ', ', `TenPX`) AS `DiaChi`, `TenQH`, `MaHD`, 
                `SoLuong`, `SoThang`,`Gia`, (`SoLuong`*`Gia`) AS `TongGia`, (ROUND(`SoLuong`*`Gia`*`SoThang`,-3)) AS `TongGiaHD`,
                '' AS `Init`,
                `ThoiGianBatDau`, (`ThoiGianBatDau` + INTERVAL `SoThang` MONTH + INTERVAL `SoNgayBaoDuong` DAY) AS `ThoiGianKetThuc`, 
                '' AS `CotN`, '' AS `CotO`,
                `phuongthucthanhtoan`.`MaPTTT`, ROUND((ROUND(`SoLuong`*`Gia`*`SoThang`,-3)/(`SoThang`/`phuongthucthanhtoan`.`MaPTTT`)),0) AS SoTienThanhToanTungDot,
                '' AS `CotR`, '' AS `CotS`,
                `ChuTK`, `SoTK`, `TenNH`,
                FLOOR(`SoThang` / `hopdong`.`MaPTTT`) AS SoDotThanhToan, `dotthanhtoan`.`MaDTT`, `TenDTT`, `HieuLuc`, `GhiChu`
        
        FROM    `diadiem`, `hopdong`, `phuongthucthanhtoan`, `dotthanhtoan`, 
                `diachi`, `phuongxa`, `quanhuyen`, `thanhpho`
        
        WHERE `hopdong`.`MaDD` = `diadiem`.`MaDD`
            AND `hopdong`.`MaPTTT` = `phuongthucthanhtoan`.`MaPTTT`
            AND  (DAY(`ThoiGianBatDau`) BETWEEN `dotthanhtoan`.`NgayBatDau` AND `dotthanhtoan`.`NgayKetThuc`)
            AND `diachi`.`MaDC` = `diadiem`.`MaDC`
            AND `phuongxa`.`MaPX` = `diachi`.`MaPX`
            AND `quanhuyen`.`MaQH` = `diachi`.`MaQH`
            AND `thanhpho`.`MaTP` = `diachi`.`MaTP`
            AND `HieuLuc` = 1
            AND ((MONTH('$date')-MONTH(`ThoiGianBatDau`)) MOD `hopdong`.`MaPTTT`) = 0
            AND    (DAY('$date')>15)+1 = `MaDTT` 
        ORDER BY `diachi`.`MaQH`
        ;
    ";
        $result = mysql_query($query);
    }

    // Load template from file
    $objPHPExcel = PHPExcel_IOFactory::load('report.xls');

    // Set properties excel
    if (1) {
        $objPHPExcel->getProperties()->setCreator("Tran Quoc Minh");
        $objPHPExcel->getProperties()->setLastModifiedBy("Tran Quoc Minh");
        $objPHPExcel->getProperties()->setTitle("Payment Request");
        $objPHPExcel->getProperties()->setSubject("minhtran@targetmedia.vn");
        $objPHPExcel->getProperties()->setDescription("Created by tranquocminh1112@gmail.com");
    }

    // Setting
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->setTitle('HCM');
    $date = strtolower(filter_input(INPUT_POST, "dateExport"));

    $begin_row = 7;
    $end_row = $begin_row + mysql_num_rows($result) - 1;
    $col = 25;

    // Template
    if (1) {
        PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
        $objPHPExcel->getActiveSheet()->setCellValue('C4', $date);
        $objPHPExcel->getActiveSheet()->getStyle('C4')->getNumberFormat()->setFormatCode('dd-mmm-yy');

// Header
        $objPHPExcel->getActiveSheet()->setCellValue('S5', "Request\n$date");
        $objPHPExcel->getActiveSheet()->setCellValue('Y5', "$date");

//    // Autosize columns
//    for ($c = 'C'; $c <= 'Y'; $c++) {
//        $objPHPExcel->getActiveSheet()->getColumnDimension($c)->setAutoSize(true);
//    }
// Border data
        $objPHPExcel->getActiveSheet()->getStyle('A' . $begin_row . ':A' . $end_row)->getBorders()->getLeft()->setBorderStyle('medium');
        $objPHPExcel->getActiveSheet()->getStyle('Y' . $begin_row . ':Y' . $end_row)->getBorders()->getRight()->setBorderStyle('medium');
        for ($char = 'A'; $char < 'Y'; $char++) {
            $objPHPExcel->getActiveSheet()->getStyle($char . $begin_row . ':' . $char . $end_row)->getBorders()->getRight()->setBorderStyle('thin');
        }
        for ($row = $begin_row; $row < $end_row; $row++) {
            $objPHPExcel->getActiveSheet()->getStyle('A' . $row . ':Y' . $row)->getBorders()->getBottom()->setBorderStyle('hair');
        }
        $objPHPExcel->getActiveSheet()->getStyle('A' . $end_row . ':Y' . $end_row)->getBorders()->getBottom()->setBorderStyle('medium');
    }

    // Insert data
    while ($item = mysql_fetch_array($result)) {
        $char = 'A';

        for ($i = 0; $i < 24; $i++) {
            if ($i == 11 || $i == 12) {
                PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
                $objPHPExcel->getActiveSheet()->setCellValue($char . $begin_row, $item[$i]);
                $objPHPExcel->getActiveSheet()->getStyle($char++ . $begin_row)->getNumberFormat()->setFormatCode('dd-mmm-yy');
            } else if ($i == 13) {
                $objPHPExcel->getActiveSheet()->setCellValue($char++ . $begin_row, '=ROUND(C4-L' . $begin_row . ', -1)/30');
            } else if ($i == 14) {
                $objPHPExcel->getActiveSheet()->setCellValue($char++ . $begin_row, "=ROUND(FLOOR(N$begin_row,1)/P$begin_row,0)*P$begin_row");
            } else if ($i == 17) {
                $str = "=IF(P$begin_row=1,\"Tháng thứ \",IF(P$begin_row=3,\"Quý thứ \",IF(P$begin_row=6,\"Nửa năm thứ \",IF(P$begin_row=12,\"Năm thứ \",\"\"))))&(O$begin_row+P$begin_row)/P$begin_row";
                $objPHPExcel->getActiveSheet()->setCellValue($char++ . $begin_row, $str);
            } else if ($i == 19) {
                $str = "=(O$begin_row/P$begin_row)*Q$begin_row";
                $objPHPExcel->getActiveSheet()->setCellValue($char++ . $begin_row, $str);
            } else if ($i == 20) {
                $str = "=J$begin_row-T$begin_row";
                $objPHPExcel->getActiveSheet()->setCellValue($char++ . $begin_row, $str);
            } else if ($i == 21) {
                $objPHPExcel->getActiveSheet()->setCellValue($char++ . $begin_row, $item[$i - 2]);
            } else if ($i == 22) {
                $objPHPExcel->getActiveSheet()->setCellValue($char++ . $begin_row, '="' . $item[$i - 2] . '"');
            } else if ($i == 23) {
                $objPHPExcel->getActiveSheet()->setCellValue($char++ . $begin_row, $item[$i - 2]);
            } else {
                $objPHPExcel->getActiveSheet()->setCellValue($char++ . $begin_row, $item[$i]);
            }
        }
        $begin_row++;
    }

    // Redirect output to a client’s web browser (Excel5) 
    if (1) {
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('mail.xls');

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Payment_Monitor_Report_LCD_Cafe-Request_Thang_9_Dot_1.xls"');
        header('Cache-Control: max-age=0');

        $objWriter->save('php://output');
    }
}

if ($action == "mail") {
    // Not submit
    $submit = strtolower(filter_input(INPUT_POST, "submit"));
    $mailName = strtolower(filter_input(INPUT_POST, "mailName"));

    // Load email
    $query = "select * from Email";
    $result = mysql_query($query);
    if (mysql_num_rows($result) > 0) {
        while ($row = mysql_fetch_array($result)) {
            $email[] = $row;
        }
        $smarty->assign("email", $email);
    }

    // Xóa email
    if ($content == "delete") {
        $query = "select * from Email where MaEmail=$id";
        $result = mysql_query($query);
        if (mysql_num_rows($result) == 1) {
            $query = "delete from Email where MaEmail=$id";
            $result = mysql_query($query);
            header("Location: /export/mail");
        }
    }

    // submit add
    if ($submit == "add") {
        $email = strtolower(filter_input(INPUT_POST, "email"));
        $query = "select * from Email where Email='$email'";
        $result = mysql_query($query);
        if (mysql_num_rows($result) > 0) {
            $success = -1;
        } else {
            $query_insert = "insert into Email(`Email`) values('$email')";
            $result_insert = mysql_query($query_insert);
            if ($result_insert) {
                $success = 1;
            } else {
                $success = 0;
            }
        }
        $smarty->assign("success", $success);
    }

    // submit send email
    if ($submit == "send mail") {
        $mail = new PHPMailer_Target();

        foreach ($email as $e) {
            $mail->addAddress($e['Email']);
        }
        $mail->Subject = "Payment Request";

        $mail->Body = "Test";

        $mail->addAttachment(WEB_DIR . "mail.xls");
        
        if (!$mail->Send()) {
            $error = $mail->ErrorInfo;
            $smarty->assign("error", $error);
        } else {
            $error = "0";
            $smarty->assign("error", $error);
        }
    }

    $smarty->display("export.tpl");
}

